
package db;

import com.mysql.jdbc.Connection;
import java.io.IOException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * <p>Class håndterer alle operasjoner som er knyttet til databaser , alle forespørsler ligger her
 * <p>url, username and password in config.properties file. OBS! don't edit config.properties, all data must be encrypted before,
 * use methods from <code>Config.class</code> to store and retrieve values.
 * @author 490501
 * @version 1.0.2
 * version releas notes:
 * 
 * 1.0.1
 *  * hash computing function goes through iteration process with 500 iterations to slow speed of possible brute-forse
 * 1.0.2
 *  * added function for getting geospatial data.
 *      <code>getFylker()</code> and <code>getKommuner(int fylkeId)</code>
 */
public class MysqlAdapter {
    private String url;
    private Connection con;
    private String name;
    private String pass;
    private PreparedStatement pstmt;
    /**
     * 
     */
    private Config conf;
    
    /**
     * <p>Constructor open connection to database 
     * <p>uses setting from config.properties
     * <p>if any error occurs, <b>try to run Config.class first</b>, to write values to property file
     * 
     */
    public MysqlAdapter() {
        try {
            conf = new Config();
            Class.forName("com.mysql.jdbc.Driver");
            url = conf.getParameter("db.host")+conf.getParameter("db.name");
            name = conf.getParameter("db.user");
            pass = conf.getParameter("db.pass");
            con = (Connection) DriverManager.getConnection(url,name, pass);          
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        } catch(SQLException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        } catch(IOException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        }    
    }
    
    /**
     * 
     * @param name name of user which must be checked
     * @return true if user exists in database, otherwise false
     * @throws SQLException if any SQLException throws
     * @since 1.0.0
     */
    public boolean checkIfNameExists(String name) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT * FROM users WHERE user=?");
        pstmt.setString(1, name);
        ResultSet executeQuery = pstmt.executeQuery();
        return executeQuery.next();        
    }
    
    /**
     * <p>
     * @param email email of user which must be checked
     * @return true if email exists in database, false - otherwise
     * @throws SQLException if any SQLException throws
     * @since 1.0.0
     */
    public boolean checkIfEmailExists(String email) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT * FROM users WHERE email=?");
        pstmt.setString(1, email);
        ResultSet executeQuery = pstmt.executeQuery();
        return executeQuery.next();   
    }
    
    /**
     * @since 1.0.0
     * <p>Saves new user in database
     * @param name username that will be stored in database
     * @param email - brukerens epost
     * @param password - passord til bruker
     * @return true if user have been stored successfully or throws Exception
     * @throws Exception if user or email exist in database
     */
    public boolean addUserToDataBase(String name, String email, String password) throws Exception {
        //Sjekker alt en gang til
        if(checkIfNameExists(name)) {
            throw new Exception("username exixts in database");
        }
        if(checkIfEmailExists(email)) {
            throw new Exception("email exists in database");
        }
        String passHash = getHash(password);
        pstmt = (PreparedStatement) con.prepareStatement("INSERT INTO users (user, passwd, email) values(?,?,?)");
        pstmt.setString(1, name);
        pstmt.setString(2, passHash);
        pstmt.setString(3, email);
        pstmt.executeUpdate();
        return true;
    }
    
    public boolean addUsagePattern(String name, String type, String values,int userId) throws Exception {
        pstmt = (PreparedStatement) con.prepareStatement("INSERT INTO UsageProfile (name, type, pattern,userID) values(?,?,?,?)");
        pstmt.setString(1, name);
        pstmt.setString(2, type);
        pstmt.setString(3, values);
        pstmt.setInt(4, userId);
        pstmt.execute();
        return true;
    }
    
    /***
     * 
     * @return
     * @throws SQLException 
     */
    public ArrayList<String> getUsagePatternNames(String _type) throws SQLException{
        pstmt = (PreparedStatement)con.prepareStatement("Select name from UsageProfile where type=?");
        pstmt.setString(1, _type);
        ResultSet result = pstmt.executeQuery();
        ArrayList<String> res = new ArrayList<String>();
        while(result.next()){
            res.add(result.getString(1));
        }
        return res;
    }
    
    /**
     * <p>returns md5 hash (salted) of given data
     * <b>OBS! hash computes 500 times, with common salt before loop and every round with new salt(round number)</b>
     * @param data data to hashing
     * @return md5 hash of given data
     * @throws IOException if any IO exceptions oppstår
     * @since 1.0.1
     */
    private String getHash(String data) throws IOException {
        MessageDigest md = null;
        try {
            md = MessageDigest.getInstance("MD5");
        } catch (NoSuchAlgorithmException ex) {
            Logger.getLogger(MysqlAdapter.class.getName()).log(Level.SEVERE, null, ex);
        }
        md.reset();
        String salt = conf.getParameter("db.salt");
        String s = data+salt; //hemmelig saltingsmetode
        for(int i = 0; i < 500; i++) {
            s += i;
            byte[] passByte = s.getBytes();
            byte[] hash = md.digest(passByte);
            StringBuilder result = new StringBuilder();
            for (int j = 0; j < hash.length; j++) {
                result.append(Integer.toHexString(0xFF & hash[j]));
            }
            s = result.toString();
        }
        return s;
    }
    
    /**
     * 
     * @param name name of user 
     * @param password password  
     * @return id for autentificated user or <strong>-1</strong> if user does not exist or password is invalid
     * @throws NoSuchAlgorithmException
     * @throws FileNotFoundException
     * @throws IOException
     * @throws SQLException 
     * @sinse 1.0.0
     */
    public int autentificateUser(String name, String password) throws IOException, SQLException {
        password = getHash(password);
        pstmt = (PreparedStatement) con.prepareStatement("SELECT id FROM users WHERE user = ? AND passwd = ?");
        pstmt.setString(1, name);
        pstmt.setString(2, password);
        ResultSet result = pstmt.executeQuery();
        if(result.next()) {
            return result.getInt(1);
        }
        else {
            return -1;
        }        
    }
    
    /**
     * <p>returns all names of buildings standarts from database u_values
     * @since 1.0.0
     * @return  ArrayList med navn til alle byggeforskrifter i databasen
     */
    public ArrayList<String> getStandarts() throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT name FROM u_values");
        ResultSet result = pstmt.executeQuery();
        ArrayList<String> res = new ArrayList<String>();
        while(result.next()) {
            res.add(result.getString(1));
        }
        return res;
    }
    
    /**
     * <p>Returns  u values for given standart
     * @param stdName navn til byggestandard
     * @return u-verdier som tilhører gitt standard
     * @throws SQLException if any SQL exception throws
     * @since 1.0.0
     */
    public double[] getStandartValues(String stdName) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT outer_wall, roof, floor, doors_and_windows FROM u_values WHERE name = ?");
        pstmt.setString(1, stdName);
        ResultSet result = pstmt.executeQuery();
        if(result.next()) {
            double[] res = new double[4];
            for(int i = 0; i<4; i++) {
                res[i] = result.getDouble(i+1);
            }
            return res;
        }
        return null;
    }
    

    /**
     * Returns 24 float values, (one for every hour of the day,) that decides 
     * how many percent of one inventory-category is used.
     * @param inhabitantsID family ID constant
     * @param category name of category
     * @return 
     */
    public float[] getInventoryUseage(short inhabitantsID, String category, boolean onHoliday) throws SQLException
    {
        //prepared statement? SQL injectio waiting for you. Timur.
        pstmt = (PreparedStatement) con.prepareStatement("SELECT time_of_day, holidays "
                + "FROM  `usage_pattern` " 
                + "WHERE idinhabitants = " + inhabitantsID + " " 
                + "AND category = " + category);
        //System.out.println("QUERY: " + pstmt.toString());
        ResultSet result = pstmt.executeQuery();
        
        if(result.next())
        {
           // float res = result.getFloat(0); 
            if(onHoliday)
            {
                return convertFromStringToFloatArray(result.getString(2));
            }
            else    
            {
                return convertFromStringToFloatArray(result.getString(1));
            }
        }
        return null;
    }
    /*
     *  SELECT furniture.idhousehold_articles, furniture.quantity,
        household_articles.energy_usage, household_articles.name, household_articles.category_category
        FROM furniture, household_articles
        WHERE furniture.idhousehold_articles = household_articles.idhousehold_articles
        AND furniture.idstructure = $
     */
    public ArrayList<String[]> getHouseProfile(int profileID) throws SQLException
    {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT furniture.idhousehold_articles, furniture.quantity, "
                +"household_articles.energy_usage, household_articles.name, household_articles.category_category "
                + "FROM furniture, household_articles WHERE furniture.idhousehold_articles = household_articles.idhousehold_articles "
                + "AND furniture.idstructure =? ");
        pstmt.setInt(1, profileID);
        ResultSet result = pstmt.executeQuery();
        ArrayList<String[]> output = new ArrayList<String[]>();
        
        while(result.next())
        {
            //output.add(result.getString(1));
            String[] str = new String[5];
            for(int i = 0; i < str.length; i++)
            {
                str[i] = result.getString(i+1);
            }
            output.add(str);
        }
        return output;
    }
    /**
     *  Returns an ArrayList<> with values that represents max energy usage for all categories.
     * If max energy is not set, float-value is set to 0.
     * @param profileID ID value that belongs to the building/area
     * @return 
     */
    public ArrayList<String> getMaxEnergyUsage(int profileID) throws SQLException
    {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT energy_usage FROM category_usage where idstructure =?");
        pstmt.setInt(1, profileID);
        ResultSet result = pstmt.executeQuery();
        ArrayList<String> output = new ArrayList<String>();
     
        while(result.next()) {
            output.add(result.getString(1));
        }
        return output;
    }
    
    
    private float[] convertFromStringToFloatArray(String input)
    {
        String spaceRemoved = input.replaceAll("\\s++", "");
        String[] stringArray = spaceRemoved.split(",");
        float[] floatArray = new float[stringArray.length];
        
        for(int i = 0; i < floatArray.length; i++)
        {
            floatArray[i] = new Float(stringArray[i]);
        }
        
        return floatArray;
    }
    

    /**
     * <p>Returns location of given postnumber in form of lattitude and longitude
     * @param _postnumber postnumber 
     * @return float array with values of lattitude and longitude
     * @throws SQLException if any SQLException occurs
     * @since 1.0.2
     */
    public float[] getLocationByPostNumber(int _postnumber) throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT lat,lon FROM postnumre where postnummer= ?");
        pstmt.setInt(1, _postnumber);
        ResultSet result = pstmt.executeQuery();
        if(result.next()) {
            float[] res = new float[2];
            for(int i = 0; i<2; i++) {
                res[i] = result.getFloat(i+1);
            }
            return res;
        }
        return null;    
    }
    
    
    /**
     * 
     * @return
     * @throws SQLException 
     * @since 1.0.2
     */
    public HashMap<Integer,String> getFylker() throws SQLException {
        pstmt = (PreparedStatement) con.prepareStatement("SELECT * FROM fylker");
        ResultSet result = pstmt.executeQuery();
        HashMap<Integer,String> _result = new HashMap<Integer, String>();
        while(result.next()){
            _result.put(result.getInt("fylkeID"), result.getString("fylkeNavn"));
        }
        return _result;
    }
    
    /**
     * 
     * @param _fylkeId
     * @return
     * @throws SQLException 
     * @since 1.0.2
     */
    public HashMap<Integer, String> getKommuner(int _fylkeId) throws SQLException{
        pstmt = (PreparedStatement) con.prepareStatement("SELECT kommuneID, kommuneNavn FROM kommuner where fylkeID = ?");
        pstmt.setInt(1, _fylkeId);
        ResultSet result = pstmt.executeQuery();
        HashMap<Integer,String> _result = new HashMap<Integer, String>();
        while(result.next()){
            _result.put(result.getInt("kommuneID"), result.getString("kommuneNavn"));
        }
        return _result;
    }
    
    public String getUsagePattern(String _type, String _name) throws SQLException{
        pstmt = (PreparedStatement)con.prepareStatement("SELECT pattern from UsageProfile where type=? AND name=?");
        pstmt.setString(1, _type);
        pstmt.setString(2, _name);
        ResultSet temp = pstmt.executeQuery();
        int[] result = new int[24];
        int index = 0;
        temp.next();
        return temp.getString(1);
    }
    
    
    //test area use only for testing, don't put any code here

    //*******************************************test**************************************//
    public static void main(String[] args) throws SQLException, IOException, Exception {
        MysqlAdapter ms  = new MysqlAdapter();
        //ms.getHouseProfile(2);
        //System.out.println(ms.getMaxEnergyUsage(2));
        //ArrayList<String> res = ms.getStandarts();
        //System.out.println(res.get(1));
            
        //ArrayList<String> res = ms.getStandarts();
        //System.out.println(res.get(1));
        ms.addUsagePattern("name", "type", "hui",14);
        //System.out.println(ms.autentificateUser("Tim2", "password"));
        //System.out.println(ms.getLocationByPostNumber(8515)[0]);
    } 
}
